﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Itegracion.Framework.Forms;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace Itegracion.iAlmacen.Model
{
    public class Producto
    {
        public static List<Element> TraerListado(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT
                  p.id, p.codigo, p.nombre, p.idMarca, m.nombre as marca, c.nombre as familia
                FROM
                  Producto p
                  JOIN Constante c ON c.codigo = 'Familia' AND p.idFamilia = c.oid
                  JOIN Constante m ON m.codigo = 'Marca' AND p.idMarca = m.oid
                ORDER BY
                  p.id DESC");
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
        public static List<Element> Insertar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                INSERT INTO Producto(codigo, nombre, idMarca, idFamilia) VALUES(@codigo, @nombre, @idMarca, @idFamilia)
                SELECT @@IDENTITY
            ");
            db.AddInParameter(command, "codigo", DbType.String, entrada[1].Value);
            db.AddInParameter(command, "nombre", DbType.String, entrada[2].Value);
            db.AddInParameter(command, "idMarca", DbType.Int32, entrada[3].Value);
            db.AddInParameter(command, "idFamilia", DbType.Int32, entrada[4].Value);
            object id = db.ExecuteScalar(command);
            
            foreach (DataRow dr in (entrada[5].Value as DataTable).Rows)
            {
                command = db.GetSqlStringCommand(@"
                    INSERT INTO Equivalencia(idProducto, cantidad, idUnidad) VALUES(@idProducto, @cantidad, @idUnidad)
                ");
                db.AddInParameter(command, "idProducto", DbType.Int32, id);
                db.AddInParameter(command, "cantidad", DbType.Decimal, dr["cantidad"]);
                db.AddInParameter(command, "idUnidad", DbType.Int32, dr["idUnidad"]);
                db.ExecuteNonQuery(command);
            }
            foreach (DataRow dr in (entrada[6].Value as DataTable).Rows)
            {
                command = db.GetSqlStringCommand(@"
                    INSERT INTO Precio(idProducto, idUnidad, p1, p2, p3, p4, p5, p6) VALUES(@idProducto, @idUnidad, @p1, @p2, @p3, @p4, @p5, @p6)
                ");
                db.AddInParameter(command, "idProducto", DbType.Int32, id);
                db.AddInParameter(command, "idUnidad", DbType.Int32, dr["idUnidad"]);
                db.AddInParameter(command, "p1", DbType.Decimal, dr["p1"]);
                db.AddInParameter(command, "p2", DbType.Decimal, dr["p2"]);
                db.AddInParameter(command, "p3", DbType.Decimal, dr["p3"]);
                db.AddInParameter(command, "p4", DbType.Decimal, dr["p4"]);
                db.AddInParameter(command, "p5", DbType.Decimal, dr["p5"]);
                db.AddInParameter(command, "p6", DbType.Decimal, dr["p6"]);
                db.ExecuteNonQuery(command);
            }
            rpta.Add(new Element("rpta", "OK"));

            return rpta;
        }
        public static List<Element> Modificar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                UPDATE Producto SET codigo=@codigo, nombre=@nombre, idMarca=@idMarca, idFamilia=@idFamilia WHERE id = @id
                    DELETE Equivalencia WHERE idProducto = @id
                    DELETE Precio WHERE idProducto = @id
                    DELETE Existencia WHERE idProducto = @id
            ");
            db.AddInParameter(command, "id", DbType.String, entrada[0].Value);
            db.AddInParameter(command, "codigo", DbType.String, entrada[1].Value);
            db.AddInParameter(command, "nombre", DbType.String, entrada[2].Value);
            db.AddInParameter(command, "idMarca", DbType.String, entrada[3].Value);
            db.AddInParameter(command, "idFamilia", DbType.Int32, entrada[4].Value);
            db.ExecuteNonQuery(command);

            foreach (DataRow dr in (entrada[5].Value as DataTable).Rows)
            {
                command = db.GetSqlStringCommand(@"
                    INSERT INTO Equivalencia(idProducto, cantidad, idUnidad) VALUES(@idProducto, @cantidad, @idUnidad)
                ");
                db.AddInParameter(command, "idProducto", DbType.Int32, entrada[0].Value);
                db.AddInParameter(command, "cantidad", DbType.Decimal, dr["cantidad"]);
                db.AddInParameter(command, "idUnidad", DbType.Int32, dr["idUnidad"]);
                db.ExecuteNonQuery(command);
            }
            foreach (DataRow dr in (entrada[6].Value as DataTable).Rows)
            {
                command = db.GetSqlStringCommand(@"
                    INSERT INTO Precio(idProducto, idUnidad, p1, p2, p3, p4, p5, p6) VALUES(@idProducto, @idUnidad, @p1, @p2, @p3, @p4, @p5, @p6)
                ");
                db.AddInParameter(command, "idProducto", DbType.Int32, entrada[0].Value);
                db.AddInParameter(command, "idUnidad", DbType.Int32, dr["idUnidad"]);
                db.AddInParameter(command, "p1", DbType.Decimal, dr["p1"]);
                db.AddInParameter(command, "p2", DbType.Decimal, dr["p2"]);
                db.AddInParameter(command, "p3", DbType.Decimal, dr["p3"]);
                db.AddInParameter(command, "p4", DbType.Decimal, dr["p4"]);
                db.AddInParameter(command, "p5", DbType.Decimal, dr["p5"]);
                db.AddInParameter(command, "p6", DbType.Decimal, dr["p6"]);
                db.ExecuteNonQuery(command);
            }
            foreach (DataRow dr in (entrada[7].Value as DataTable).Rows)
            {
                command = db.GetSqlStringCommand(@"
                    INSERT INTO Existencia(idProducto, idAlmacen, idUnidad, cantidad, precioUnitario, vencimiento, nroLote) 
                        VALUES(@idProducto, @idAlmacen, @idUnidad, @cantidad, @precioUnitario, @vencimiento, @nroLote)
                ");
                db.AddInParameter(command, "idProducto", DbType.Int32, entrada[0].Value);
                db.AddInParameter(command, "idAlmacen", DbType.Int32, dr["idAlmacen"]);
                db.AddInParameter(command, "idUnidad", DbType.Int32, dr["idUnidad"]);
                db.AddInParameter(command, "cantidad", DbType.Decimal, dr["cantidad"]);
                db.AddInParameter(command, "precioUnitario", DbType.Decimal, dr["precioUnitario"]);
                db.AddInParameter(command, "vencimiento", DbType.String, dr["vencimiento"]);
                db.AddInParameter(command, "nroLote", DbType.String, dr["nroLote"]);
                db.ExecuteNonQuery(command);
            }
            rpta.Add(new Element("rpta", "OK"));

            return rpta;
        }
        public static List<Element> Traer(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT p.id, p.codigo, p.nombre, p.idMarca, p.idFamilia
                    FROM Producto p WHERE p.id = @id
                SELECT idProducto, cantidad, idUnidad 
	                FROM Equivalencia WHERE idProducto = @id
                SELECT idProducto, idUnidad, p1, p2, p3, p4, p5, p6 
	                FROM Precio WHERE idProducto = @id
                SELECT idProducto, idAlmacen, idUnidad, cantidad, precioUnitario, vencimiento, nroLote 
	                FROM Existencia WHERE idProducto = @id
            ");
            db.AddInParameter(command, "id", DbType.Int32, entrada[0].Value);
            DataSet ds = db.ExecuteDataSet(command);
            rpta.AddRange(ds.Tables[0].Rows.ToList());
            rpta.Add(new Element("unidad", ds.Tables[1]));
            rpta.Add(new Element("precios", ds.Tables[2]));
            rpta.Add(new Element("existencias", ds.Tables[3]));

            return rpta;
        }
        public static List<Element> Navegar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT idProducto, idUnidad, p1, p2, p3, p4, p5, p6 
	                FROM Precio WHERE idProducto = @id
                SELECT idProducto, idAlmacen, idUnidad, cantidad, precioUnitario, vencimiento, nroLote 
	                FROM Existencia WHERE idProducto = @id
            ");
            db.AddInParameter(command, "id", DbType.Int32, entrada[0].Value);
            DataSet ds = db.ExecuteDataSet(command);
            rpta.Add(new Element("precios", ds.Tables[0]));
            rpta.Add(new Element("existencias", ds.Tables[1]));

            return rpta;
        }
        public static List<Element> Nuevo(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT TOP 1 0 as idProducto, 1.00 as cantidad, 1 as idUnidad
	                FROM Equivalencia
                SELECT TOP 1 0 as idProducto, 1 as idUnidad, 0.00 as p1, 0.00 as p2, 0.00 as p3, 0.00 as p4, 0.00 as p5, 0.00 as p6 
	                FROM Precio
                SELECT TOP 0 0 as idProducto, idAlmacen, idUnidad, cantidad, precioUnitario, vencimiento, nroLote 
	                FROM Existencia
            ");
            DataSet ds = db.ExecuteDataSet(command);
            rpta.Add(new Element("unidad", ds.Tables[0]));
            rpta.Add(new Element("precios", ds.Tables[1]));
            rpta.Add(new Element("existencias", ds.Tables[2]));

            return rpta;
        }
        public static List<Element> Eliminar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                DELETE FROM Producto WHERE id = @id");
            db.AddInParameter(command, "id", DbType.Int32, entrada[0].Value);
            db.ExecuteNonQuery(command);
            rpta.Add(new Element("rpta", "OK"));

            return rpta;
        }
    }
}
